package SpdgBI.Finance;

import SpdgBI.Common.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class FinancialStmts {
    private static Connection connBI = DBUtils.getConnection("SPDGBI");
    public  FinancialStmts(){}
    public static void PumpDataByParam(String unitList, String stmtsList, String startDate, String endDate) {
        Connection connBi = null;
        Connection connFinStmts = null;
        PreparedStatement pstmtPumpdata;
        Long endTime;
        Long StartTime;
        try {
            StringBuffer strSqlUnit = new StringBuffer();
            String strUpd = "";
            connBi = DBUtils.getConnection("SPDGBI");
            connFinStmts = DBUtils.getConnection("FINSTMTS");
            Statement stmtSrcData = null;
            connBi.setAutoCommit(false);
            strSqlUnit.append("SELECT UNITCODE,UNITNAME,BRANCH,FINREPCODE FROM MD_UNIT WHERE 1=1 ");
            String[] lstUnit = unitList.split(",");
            if (!"".equals(lstUnit[0])) {
                strSqlUnit.append(" AND UNITCODE IN (");
                for (int i = 0; i < lstUnit.length; i++) {
                    strSqlUnit.append("'" + lstUnit[i] + "'");
                    if (i != lstUnit.length - 1) {
                        strSqlUnit.append(",");
                    }
                }
                strSqlUnit.append(")");
            }
            strSqlUnit.append(" ORDER BY UNITCODE");
            Statement stmtUnit = connBi.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //connBi.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ResultSet rsUnit = stmtUnit.executeQuery(strSqlUnit.toString());
            /*
            List<List<String>> aryUnits = new ArrayList<List<String>>();
            List<String> aryUnit = null;
            ResultSetMetaData rsmd = rs.getMetaData();//ResultSet 对象的列的编号、类型和属性
            list = new ArrayList<String>();
            i = rsmd.getColumnCount();
            for(int j=1;j<=i;j++)
            {
                list.add(rsmd.getColumnLabel(j));//输出和显示的指定列的建议标题
                //list.add(getColumnName(j));//输出和显示的原数据列名
            }

            while (rsUnit.next()) {
                aryUnit = new ArrayList<String>();

                 for(int j=1;j<=i;j++)
                 {
                   list.add(rs.getString(j));
                 }

                aryUnit.add(rsUnit.getString(1));
                aryUnit.add(rsUnit.getString(2));
                aryUnit.add(rsUnit.getString(3));
                aryUnit.add(rsUnit.getString(4));
                aryUnits.add(aryUnit);
            }
            */
            PreparedStatement pstmtPeriod = connBi.prepareStatement("SELECT DIMYEAR,DIMMONTH,STARTDATE,ENDDATE FROM MD_PERIOD WHERE startdate>= ? AND enddate<= ?");
            pstmtPeriod.setString(1, startDate);
            pstmtPeriod.setString(2, endDate);
            ResultSet rsPeriod = pstmtPeriod.executeQuery();

            strSqlUnit.delete(0, strSqlUnit.length());

            String[] aryStmts;
            if (stmtsList.length() != 0) {
                aryStmts = stmtsList.split(",");
            } else {
                aryStmts = new String[]{"FINREPBS", "FINREPIS", "FINREPCFS", "FINREPES"};
            }

            StringBuffer strSqlDel = new StringBuffer();
            StringBuffer strSqlIns = new StringBuffer();
            StringBuffer strSqlUpd = new StringBuffer();
            StringBuffer strSqlSrcData = new StringBuffer();
            PreparedStatement pstmtMeasure = null;
            Integer j;
            while (rsPeriod.next()) {
                for (String aryStmt : aryStmts) {
                    StartTime = System.currentTimeMillis();
                    strSqlDel.delete(0, strSqlUpd.length());
                    strSqlIns.delete(0, strSqlUpd.length());
                    if (aryStmt == "FINREPBS") {
                        strSqlDel.append("delete from base_bs where unitcode = ? and dimyear=? and dimmonth = ?");
                        strSqlIns.append("INSERT INTO BASE_BS ( UNITCODE,DIMYEAR,DIMMONTH) VALUES (?,?,?)");
                        strUpd = "UPDATE BASE_BS SET ";
                    }
                    if (aryStmt == "FINREPIS") {
                        strSqlDel.append("delete from base_is where unitcode = ? and dimyear=? and dimmonth = ?");
                        strSqlIns.append("INSERT INTO BASE_IS ( UNITCODE,DIMYEAR,DIMMONTH) VALUES (?,?,?)");
                        strUpd = "UPDATE BASE_IS SET ";
                    }
                    if (aryStmt == "FINREPCFS") {
                        strSqlDel.append("delete from base_cfs where unitcode = ? and dimyear=? and dimmonth = ?");
                        strSqlIns.append("INSERT INTO BASE_CFS ( UNITCODE,DIMYEAR,DIMMONTH) VALUES (?,?,?)");
                        strUpd = "UPDATE BASE_CFS SET ";
                    }
                    if (aryStmt == "FINREPES") {
                        strSqlDel.append("delete from base_es where unitcode = ? and dimyear=? and dimmonth = ?");
                        strSqlIns.append("INSERT INTO BASE_ES ( UNITCODE,DIMYEAR,DIMMONTH) VALUES (?,?,?)");
                        strUpd = "UPDATE BASE_ES SET ";
                    }

                    //PreparedStatement pstmtDel = connBi.prepareStatement(strSqlIns.toString());
                    //PreparedStatement pstmtIns = connBi.prepareStatement(strSqlIns.toString());
                    pstmtPumpdata = connBi.prepareStatement(strSqlDel.toString());
                    rsUnit.beforeFirst();
                    while (rsUnit.next()) {
                        pstmtPumpdata.setString(1, rsUnit.getString(1));
                        pstmtPumpdata.setString(2, rsPeriod.getString(1));
                        pstmtPumpdata.setString(3, rsPeriod.getString(2));
                        pstmtPumpdata.addBatch();
                    }
                    pstmtPumpdata.executeBatch();
                    pstmtPumpdata.clearParameters();
                    pstmtPumpdata.clearBatch();
                    pstmtPumpdata = connBi.prepareStatement(strSqlIns.toString());
                    rsUnit.beforeFirst();
                    while (rsUnit.next()) {
                        pstmtPumpdata.setString(1, rsUnit.getString(1));
                        pstmtPumpdata.setString(2, rsPeriod.getString(1));
                        pstmtPumpdata.setString(3, rsPeriod.getString(2));
                        pstmtPumpdata.addBatch();
                    }
                    pstmtPumpdata.executeBatch();
                    pstmtPumpdata.executeBatch();
                    pstmtPumpdata.clearParameters();
                    pstmtPumpdata.clearBatch();

                    Statement stmtMeasure = connBi.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    ResultSet rsMeasure = stmtMeasure.executeQuery("SELECT mscode,etltb,etlfield FROM md_measure WHERE mscbcode= '" + aryStmt + "' order by mscode");
                    List<String> lstDstTb = new ArrayList<>();

                    while (rsMeasure.next()) {
                        if (lstDstTb.indexOf(rsMeasure.getString("etltb")) == -1) {
                            lstDstTb.add(rsMeasure.getString("etltb"));
                        }
                    }


                    for (int i = 0; i < lstDstTb.size(); i++) {
                        strSqlUpd.delete(0, strSqlUpd.length());
                        strSqlUpd.append(strUpd);
                        rsMeasure.beforeFirst();
                        while (rsMeasure.next()) {
                            if (rsMeasure.getString("etltb").equals(lstDstTb.get(i))) {
                                strSqlUpd.append(rsMeasure.getString("mscode"));
                                strSqlUpd.append("=?,");
                            }
                        }
                        strSqlUpd.delete(strSqlUpd.length() - 1, strSqlUpd.length());
                        strSqlUpd.append(" WHERE UNITCODE = ? AND DIMYEAR=? AND DIMMONTH=?");
                        pstmtPumpdata = connBi.prepareStatement(strSqlUpd.toString());
                        strSqlSrcData.delete(0, strSqlSrcData.length());
                        strSqlSrcData.append("SELECT NVL(b.unit_prop9,'XX') AS unitcode, decode(a.ver,'0','0','510','1','2') ver,c.* FROM iufo.iufo_measure_pubdata a,iufo.iufo_unit_info b,iufo.");
                        strSqlSrcData.append(lstDstTb.get(i));
                        strSqlSrcData.append(" C WHERE A.code = b.unit_id and (A.ver = 0 or A.ver = 510) and ");
                        strSqlSrcData.append("to_number(substr(A.time_code, 1, 4)|| substr(A.time_code, 9, 2)|| substr(A.time_code, 15, 2)) >= '");
                        strSqlSrcData.append(rsPeriod.getString(3).replace("-", ""));
                        strSqlSrcData.append("' and to_number(substr(A.time_code, 1, 4)|| substr(A.time_code, 9, 2)|| substr(A.time_code, 15, 2)) <= '");
                        strSqlSrcData.append(rsPeriod.getString(4).replace("-", ""));
                        strSqlSrcData.append("' AND A.ktype = 'gwab0qq49ns87272js5w' and A.alone_id = C.alone_id ORDER BY b.unit_prop9");
                        stmtSrcData = connFinStmts.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        ResultSet rsSrcData = stmtSrcData.executeQuery(strSqlSrcData.toString());
                            /*
                            HashMap<String, List> hmSrcData = new HashMap<String, List>();
                            hmSrcData.put(lstDstTb.get(0), DBUtils.extractData(rsSrcData));
                            List lstSrcData = new ArrayList();
                            Iterator it = hmSrcData.keySet().iterator();
                            while(it.hasNext()) {
                                String key = (String)it.next();
                                lstSrcData = hmSrcData.get(key);
                            }
                                  for (i = 0; i < lstSrcData.size(); i++) {
                                    if (((Map) lstSrcData.get(i)).get("UNITCODE") == rsUnit.getString(1) && ((Map) lstSrcData.get(i)).get("ver") == rsUnit.getString("BRANCH")) {
                                        rsMeasure.first();
                                        j = 1;
                                        while (rsMeasure.next()) {
                                            if (rsMeasure.getString(2) == key) {
                                                pstmtIns.setFloat(j++, (Float) ((Map) lstSrcData.get(i)).get(rsMeasure.getString(1)));
                                            }
                                        }
                                        break;
                                    }
                                }

                             */
                        rsUnit.beforeFirst();
                        while (rsUnit.next()) {
                            rsSrcData.beforeFirst();
                            while (rsSrcData.next()) {
                                if (rsSrcData.getString("unitcode").equals(rsUnit.getString("FINREPCODE")) && rsSrcData.getString("ver").equals(rsUnit.getString("BRANCH"))) {
                                    rsMeasure.beforeFirst();
                                    j = 1;
                                    while (rsMeasure.next()) {
                                        if (rsMeasure.getString("etltb").equals(lstDstTb.get(i))) {
                                            pstmtPumpdata.setFloat(j++, rsSrcData.getFloat(rsMeasure.getString("etlfield")));
                                        }
                                    }
                                    pstmtPumpdata.setString(j++, rsUnit.getString("UNITCODE"));
                                    pstmtPumpdata.setString(j++, rsPeriod.getString("DIMYEAR"));
                                    pstmtPumpdata.setString(j++, rsPeriod.getString("DIMMONTH"));
                                    pstmtPumpdata.addBatch();
                                    break;
                                }
                            }
                        }
                        pstmtPumpdata.executeBatch();
                        pstmtPumpdata.clearParameters();
                        pstmtPumpdata.clearBatch();
                    }
                    connBi.commit();
                    endTime = System.currentTimeMillis();
                    System.out.println("抽取数据完成，对应期间"+rsPeriod.getString("DIMYEAR")+rsPeriod.getString("DIMMONTH")+",对应报表："+aryStmt+",用时：" + (endTime - StartTime));
                }
            }

        } catch (
                Exception e)

        {
            e.printStackTrace();
        } finally

        {
            DBUtils.close(connBi);
            DBUtils.close(connFinStmts);


        }

    }

    public void PumpDataAll(){
        //从MD_MEND表中取出对应期间的报表上报状态，完成则进行抽取，未完成则退出，抽取后把报表抽取状态置成抽取完成
        Connection connBi = null;
        PreparedStatement pstmtMend = null;
        ResultSet rsMend = null;
        String strRepCommit = "";
        String strPeriod = "";
        try{
            connBi = DBUtils.getConnection("SPDGBI");
            pstmtMend = connBi.prepareStatement("select PPVALUE from SYS_PROPERTIES where ppcode = '001'");
            rsMend = pstmtMend.executeQuery();
            while (rsMend.next()){
                strPeriod = rsMend.getString("PPVALUE");
            }

            pstmtMend = connBi.prepareStatement("SELECT FINSTMTS_COMMIT FROM MD_MEND WHERE DIMYEAR = ? AND DIMMONTH = ?");
            pstmtMend.setString(1,strPeriod.substring(1,4));
            pstmtMend.setString(2,strPeriod.substring(6,7));
            rsMend = pstmtMend.executeQuery();
            while (rsMend.next()){
                strRepCommit= rsMend.getString("FINSTMTS_MEND");
            }

            if (strRepCommit == "1"){
                pstmtMend = connBi.prepareStatement("SELECT DIMYEAR,DIMMONTH,STARTDATE,ENDDATE FROM MD_PERIOD WHERE DIMYEAR = ? AND DIMMONTH = ?");
                pstmtMend.setString(1,strPeriod.substring(1,4));
                pstmtMend.setString(2,strPeriod.substring(6,7));
                rsMend = pstmtMend.executeQuery();
                while (rsMend.next()) {
                    PumpDataByParam("", "", rsMend.getString("STARTDATE"), rsMend.getString("ENDDATE"));
                    pstmtMend = connBi.prepareStatement("UPDATE MD_MEND SET FINSTMTS_DP = '1' WHERE DIMYEAR = ? AND DIMMONTH = ?");
                    pstmtMend.setString(1, strPeriod.substring(1, 4));
                    pstmtMend.setString(2, strPeriod.substring(6, 7));
                    rsMend = pstmtMend.executeQuery();
                }
            }

        }catch(Exception ex){

        }
    }

    public static void GetRepCommitFlag(){

        CallableStatement cstmtDp = null;
        try {
            cstmtDp = connBI.prepareCall("{call Get_FinRep_Commit()}");
            //stmt.registerOutParameter(3, Types.INTEGER);
            cstmtDp.execute();
            //int i = stmt.getInt(3);
        }
        catch (Exception e){
            e.printStackTrace();
        }finally {
            //DBUtils.close(connBI);
        }
    }
}
